SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE dynamic_search_bad
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL,
       @employeestr varchar(MAX) = NULL,
       @debug       bit = 0 AS

DECLARE @sql  nvarchar(MAX),
        @nl   char(2)  = char(13) + char(10)

SELECT @sql =
   'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
           c.CustomerID, c.CustomerName, c.Address, c.City,
           c.Region, c.PostalCode, c.Country, c.Phone,
           p.ProductID, p.ProductName, p.UnitsInStock,
           p.UnitsOnOrder, o.EmployeeID
    FROM   dbo.Orders o
    JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
    JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
    JOIN   dbo.Products p ON p.ProductID = od.ProductID
    WHERE  1 = 1' + @nl

IF @orderid IS NOT NULL
   SELECT @sql += ' AND o.OrderID = ' +
                    convert(varchar(10), @orderid) + @nl

IF @status IS NOT NULL
   SELECT @sql += ' AND o.Status = ''' + @status + '''' + @nl

IF @fromdate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate >= ''' +
                        convert(char(10), @fromdate) + '''' + @nl

IF @todate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate <= ''' +
                        convert(char(10), @todate) + '''' + @nl

IF @custid IS NOT NULL
   SELECT @sql += ' AND o.CustomerID = ''' + @custid + '''' + @nl

IF @custname IS NOT NULL
   SELECT @sql += ' AND c.CustomerName LIKE ''' +
                        @custname + '%''' + @nl

IF @city IS NOT NULL
   SELECT @sql += ' AND c.City = ''' + @city + '''' + @nl

IF @region IS NOT NULL
   SELECT @sql += ' AND c.Region = ''' + @region + '''' + @nl

IF @prodid IS NOT NULL
   SELECT @sql += ' AND od.ProductID = ' +
                    convert(varchar(10), @prodid) + @nl

IF @prodname IS NOT NULL
   SELECT @sql += ' AND p.ProductName LIKE ''' +
                        @prodname + '%''' + @nl

IF @employeestr IS NOT NULL
   SELECT @sql += ' AND o.EmployeeID IN (' + @employeestr + ')' + @nl

SELECT @sql += ' ORDER BY o.OrderID'

IF @debug = 1
   PRINT @sql

EXEC(@sql)
go
-- Compare the generated SQL statements.
EXEC dynamic_search_1 @orderid =  11000, @debug = 1
EXEC dynamic_search_bad @orderid = 11000, @debug = 1

-- First returns data, second does not!
EXEC dynamic_search_1 @city = N'Łódź', @prodname = 'Stout', @debug = 1
EXEC dynamic_search_bad @city = N'Łódź', @prodname = 'Stout', @debug = 1

-- See all orders from the same city as the customer REGGC.
DECLARE @city nvarchar(25) =
        (SELECT City FROM Customers WHERE CustomerID = N'REGGC')
EXEC dynamic_search_bad @city = @city, @debug = 1

-- This appears to be complete nonsense.
EXEC dynamic_search_bad
     @employeestr = '0)
     EXEC xp_cmdshell ''DIR C:\''
     SELECT * FROM (SELECT 1 AS OrderID) AS o --', @debug = 1

